﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
using Entity;

namespace DataControls
{
   public class GIAOVIEN_Data
    {
       DataBaseAccess dataBaseAccess = new DataBaseAccess();

       public void timkiemtheo_Khoa(DataGridView dtgGiaoVien, String tenkhoa)
       {
           String excuteString = "SELECT G.* FROM GIAOVIEN G, KHOA K WHERE K.MAKHOA = G.MAKHOA AND K.TENKHOA LIKE'%" + tenkhoa + "'";
           DataTable dataTable = new DataTable();
           dataTable = dataBaseAccess.getDataTable(excuteString);
           dtgGiaoVien.DataSource = dataTable;
       }

       public void timkiemtheo_Ten(DataGridView dtgGiaoVien, String tenkhoa)
       {
           String excuteString = "SELECT * FROM GIAOVIEN G WHERE  TENGV LIKE'%" + tenkhoa + "'";
           DataTable dataTable = new DataTable();
           dataTable = dataBaseAccess.getDataTable(excuteString);
           dtgGiaoVien.DataSource = dataTable;
       }





       public void giaovien_Combobox(ComboBox cb)
       {
           String excuteString = "SELECT * FROM GIAOVIEN";
           dataBaseAccess.addto_Combobox(cb, excuteString);
       }

       public void giaovientheokhoa_Combobox(ComboBox cb, String makhoa)
       {
           String excuteString = "SELECT * FROM GIAOVIEN WHERE MAKHOA='"+ makhoa + "'";
           dataBaseAccess.addto_Combobox(cb, excuteString);
       }

       public void thongtin_GiaoVien(DataGridView dtgGiaoVien,String magiaovien)
       {
           DataTable dataTable = new DataTable();
           dataTable = dataBaseAccess.getDataTable("SELECT TENHOCVI,TENKHOA, TENCV FROM GIAOVIEN G, KHOA K, HOCVI H, CHUCVU C, CV_GIAOVIEN CV " +
                                                    "WHERE H.MAHOCVI = G.MAHOCVI AND K.MAKHOA = G.MAKHOA AND C.MACV = CV.MACV AND CV.MAGV = G.MAGV AND G.MAGV='" + magiaovien + "'");
           dtgGiaoVien.DataSource = dataTable;
       }






       public Boolean kiemtraMaGV_LUONGCB(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM BANGLUONGCB WHERE MAGV='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtraMaGV_LUONGGD(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM BANGLUONGGD WHERE MAGV='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtraMaGV_LUONGKN(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM BANGLUONGKN WHERE MAGV='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtraMaGV_NXDT(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM NX_DETAI WHERE MAGVNX='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtraMaGV_DETAI(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM DETAI WHERE MAGVHD='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtraMaGV_LOPHOC(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM LOPHOC WHERE MAGVCN='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtraMaGV_GIANGDAY(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM GIANGDAY WHERE MAGV='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtraMaGV_CV(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM CV_GIAOVIEN WHERE MAGV='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }





       public Boolean kiemtra_MaGiaoVien(String magiaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM GIAOVIEN WHERE MAGV='" + magiaovien + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }
       public Boolean kiemtra_GiaoVien(GIAOVIEN giaovien)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM GIAOVIEN WHERE TENGV='" + giaovien.tenGV +
                                                       "' AND NGAYSINH ='" + giaovien.ngaysinh +
                                                       "' AND QUEQUAN='" + giaovien.quequan+ "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;                                                         
       }




       public void layten_HocVi(String mahocvi, ComboBox cb)
       {
           int a = 0;
           String excuteString = "SELECT TENHOCVI FROM HOCVI WHERE MAHOCVI='" + mahocvi + "'";
           String tenhocvi = dataBaseAccess.excuteReader(excuteString);
           for (int i = 0; i < cb.Items.Count; i++)
           {
               cb.SelectedIndex = i;
               if (cb.Text == tenhocvi)
                   a = i;
           }
           cb.SelectedIndex = a;
       }

       public void layten_Khoa(String makhoa, ComboBox cb)
       {
           int a = 0;
           String excuteString = "SELECT TENKHOA FROM KHOA WHERE MAKHOA='" + makhoa + "'";
           String tenkhoa = dataBaseAccess.excuteReader(excuteString);
           for (int i = 0; i < cb.Items.Count; i++)
           {
               cb.SelectedIndex = i;
               if (cb.Text == tenkhoa)
                   a = i;
           }
           cb.SelectedIndex = a;
       }



       public String hocvi_GiaoVien(String magiaovien)
       {
           String tenhocvi ="";
           String excuteString = "SELECT TENHOCVI FROM GIAOVIEN G , HOCVI H WHERE G.MAGV='" + magiaovien + 
                                                                        "' AND G.MAHOCVI = H.MAHOCVI";
           tenhocvi = dataBaseAccess.excuteReader(excuteString);
           return tenhocvi;
       }

       public float hesoluong_GiaoVien( String mahocvi)
       {
            float a = 0;
            a = (float)Convert.ToDouble(dataBaseAccess.excuteScalar("SELECT HESOHV FROM HOCVI WHERE MAHOCVI='" + mahocvi + "'"));
            return a;
       }

       public float heso_chucvuGiaoVien(String magiaovien)
       {
           float a = 0;
           a = (float)Convert.ToDouble(dataBaseAccess.excuteScalar("SELECT C.LUONGCV FROM CHUCVU C, GIAOVIEN G, CV_GIAOVIEN CV " 
                                                              + "WHERE G.MAGV='" + magiaovien + "' AND C.MACV= CV.MACV AND G.MAGV = CV.MAGV ORDER BY C.LUONGCV DESC"));
           return a;
       }

       public float heso_hocviGiaoVien(String magiaovien)
       {
           float a = 0;
           a = (float)Convert.ToDouble(dataBaseAccess.excuteScalar("SELECT H.HESOHV FROM HOCVI H, GIAOVIEN G " 
                                                              + "WHERE G.MAGV='" + magiaovien + "' AND  G.MAHOCVI = H.MAHOCVI"));
           return a;
       }

       public int thamnien_GiaoVien(String magiaovien)
       {
           int a = 0;
           DateTime b = (DateTime)dataBaseAccess.excuteScalar("SELECT NGBDVL FROM GIAOVIEN "
                                                              + "WHERE MAGV='" + magiaovien + "'");
           a = (int)b.Year;
           return a;
       }

       public float luong_CanBan(String magiaovien)
       {
           float a = 0;
           a = (float)Convert.ToDouble(dataBaseAccess.excuteScalar("SELECT MUCLUONG FROM GIAOVIEN "
                                                              + "WHERE MAGV='" + magiaovien + "'"));
           return a;
       }

    }
}
